﻿
CREATE FUNCTION [dbo].[Collection_PhoneHome](
	@UsixID varchar(8),
	@CompanyID int,
	@Pos int
)RETURNS TABLE 
RETURN 
SELECT     REPLACE(ISNULL(B0.PhoneHome, CF_E.[Home Phone #]),'-','') AS PhoneHome,'H' PhoneType
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND (L_B.Pos = 0)
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND @Pos = 0
UNION ALL
SELECT     REPLACE(ISNULL(B0.PhoneHome, CF_E.[Joint H Phone#]),'-','') AS PhoneHome,'HC'
FROM         t_Borrower AS B0 INNER JOIN
                      t_Loan_Borrower AS L_B ON B0.ID = L_B.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND L_B.Pos = 1
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND @Pos = 1 AND (ISNULL(B0.PhoneHome, CF_E.[Joint H Phone#]) > '')


